#!/usr/bin/pyhton3
# coding: utf-8
# partition_with_py.py
# python脚本操作分区
# 2018-10-12
"""
命名规范：
	函数名: 2 代表 to (操作目的)
	函数名: p 代表 partition (分区)

"""

def alert_table_2_p_table(table, column):
	"""
		普通表改成分区表
		table: 表名
		column: 分区依赖的字段
	"""
	sql = (
		f"ALTER TABLE `{table}`"
		f" PARTITION BY RANGE COLUMNS({column}) (" 
	 	" PARTITION p_max VALUES LESS THAN MAXVALUE"
		" );"
	)
	print(sql)


def get_p_allow_min_v(table, max_v='MAXVALUE'):
	"""
		获取允许生成最小分区分区值
	"""
	sql = (
		"SELECT PARTITION_DESCRIPTION AS pd"
        f" FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = '{table}';"
		)
	print(sql)


def add_partition(table, max_p='p_max', max_v='MAXVALUE', p_n=15, p_interval=100000):
	"""
		增加表分区: 生产创建分区的语句仅适用按整型字段分区的表
		table: 表名
		max_p: 等待分区的大分区, 每个分区都可以再切分
		max_v: 等待分区的字段最大值默认是: MAXVALUE (无限大)
		p_n: 创建分区数量
		p_interval: 分区间隔区间默认: 每增加100000新增一个分区
		allow_min_v: 
	"""
	allow_min_v = 300000 # 允许的最小分区值大小

	add_p_sqls = ''
	for n in range(1, p_n+1):
		p_v = allow_min_v + n * p_interval
		
		if max_v != "MAXVALUE" and p_v >= max_v:
			# 分区值超出 max_v 的值数据库会报错
			break
		elif p_v <= allow_min_v:
			# 分区值小于已有分区值会报错
			continue

		add_p_sqls += f"\n PARTITION p{p_v} VALUES LESS THAN ({p_v}),"

	sql = (
		f"ALTER TABLE {table} REORGANIZE PARTITION {max_p} INTO("
		f"{add_p_sqls}"
		f"\n PARTITION {max_p} VALUES LESS THAN ({max_v})"
		" \n);"
		)
	print(sql)


def drop_partition(table, p_n='test_p_undefined'):
	"""
		删除分区
	"""
	sql = f"ALTER TABLE {table} DROP PARTITION {p_n};"

	print(sql)



def test_all():
	"测试函数"
	table = "test_table"
	column = "test_column"

	def test_alert_table_2_p_table():
		alert_table_2_p_table(table, column)

	def test_add_partition():
		add_partition(table)

	def test_get_p_allow_min_v():
		get_p_allow_min_v(table)

	def test_drop_partition():
		drop_partition(table)

	# test_alert_table_2_p_table()
	# test_add_partition()
	# test_get_p_allow_min_v()
	test_drop_partition()


if __name__ == '__main__':
	test_all()
	